unit untGeraScript;

interface

procedure GeraScript;
procedure GravaLinha(p_Texto: String);

implementation

uses clsParametros, SysUtils,dbtables, db,dm, Biblio, AtualizaBD;

var
 Arq: TextFile;
 Parametros: TParametros;

procedure GravaLinha(p_Texto: String);
begin
 Writeln(Arq,p_Texto);
end;

procedure GeraScript;
var
  constr,
  ultTabela: String;
  first    : Boolean;
  fim,
  xsql,
  constrPK : String;
  tqTabela,
  tqCampos,
  tqConst  : TQuery;
begin
 Parametros:= TParametros.Create;
 Parametros.Recuperar('1');
 AssignFile(Arq,Parametros.Repos_personal+'cria_dbh.sql');
 Rewrite(Arq);
 GravaLinha('drop user user_industrial1 cascade;');
 GravaLinha('create user user_industrial1 identified by crit;');
 GravaLinha('grant connect,resource to user_industrial1;');
 GravaLinha('commit;');
 GravaLinha('conn user_industrial1/crit@acesso_industrial;');
 GravaLinha('');

 tqTabela:= TQuery.Create(DmInt);
 tqTabela.DatabaseName:= DmInt.Db.DatabaseName;
 tqCampos:= TQuery.Create(DmInt);
 tqCampos.DatabaseName:= DmInt.Db.DatabaseName;
 tqConst := TQuery.Create(DmInt);
 tqConst.DatabaseName:= DmInt.Db.DatabaseName;

 tqTabela.Close;
 tqTabela.SQL.Clear;
 tqTabela.SQL.Add('select table_name from cat where table_type = ''TABLE'' ');
 tqTabela.Open;

 ultTabela:= '';
 first    := True;
 constrPK := '';

 FrmAtualizaBD.ProgressBar1.Position:= 0;
 FrmAtualizaBD.ProgressBar1.Max:= tqTabela.RecordCount;
 ultTabela:= tqTabela.FieldByName('TABLE_NAME').AsString;

 while not tqTabela.Eof do begin
  FrmAtualizaBD.ProgressBar1.Position:= FrmAtualizaBD.ProgressBar1.Position + 1;
  FrmAtualizaBD.Refresh;
  if not first then begin
   GravaLinha(');');
   if constrPK <> '' then
    GravaLinha('ALTER TABLE '+ultTabela+' ADD CONSTRAINT PK_'+ultTabela+' PRIMARY KEY('+constrPK+');');
   GravaLinha('');
   constrPK:= '';
  end;

  if (ultTabela <> tqTabela.FieldByName('TABLE_NAME').AsString) or (first) then
   GravaLinha('CREATE TABLE '+tqTabela.FieldByName('TABLE_NAME').AsString+' (');
  first:= False;

  tqCampos.Close;
  tqCampos.Params.Clear;
  tqCampos.SQL.Clear;
  tqCampos.SQL.Add('select Table_name ,column_name,DATA_TYPE,Data_Length,'+
                   'Data_Precision,DATA_SCALE '+
                   'from user_tab_columns '+
                   'where table_name =:TAB ORDER BY COLUMN_ID');
  tqCampos.ParamByName('TAB').AsString:= tqTabela.FieldByName('TABLE_NAME').AsString;
  tqCampos.Open;
  while not tqCampos.Eof do begin
   tqConst.Close;
   tqConst.SQL.Clear;
   tqConst.Params.Clear;
   tqConst.SQL.Add('SELECT distinct CONSTRAINT_TYPE, COLUMN_NAME '+
                   'FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B '+
                   'WHERE A.constraint_NAME = B.constraint_NAME '+
                   'and A.table_NAME = B.table_NAME '+
                   'AND A.TABLE_NAME = :Tab and a.column_name =:Col');
   tqConst.ParamByName('Tab').AsString:= tqTabela.FieldByName('TABLE_NAME').AsString;
   tqConst.ParamByName('Col').AsString:= tqCampos.FieldByName('Column_Name').AsString;
   tqConst.Open;
   constr:= '';
   while not tqConst.Eof do begin
    if tqConst.FieldByName('CONSTRAINT_TYPE').AsString = 'C' then
     constr:= constr + 'NOT NULL';

    if tqConst.FieldByName('CONSTRAINT_TYPE').AsString = 'P' then
     constrPK:= constrPK + tqConst.FieldByName('Column_Name').AsString;
    tqConst.Next;

    if tqConst.FieldByName('CONSTRAINT_TYPE').AsString = 'P' then begin
     if constrPK <> '' then begin
      if tqConst.eof then
       constrPK:= constrPK + ''
      else
       constrPK:= constrPK + ',';
     end;
    end;
   end;//fim while tqConst.eof

   xsql:= '';

   if (tqCampos.FieldByName('DATA_TYPE').AsString = 'CHAR') OR (tqCampos.FieldByName('DATA_TYPE').AsString = 'VARCHAR2') then
    xsql:=(copy(tqCampos.FieldByName('Column_Name').AsString+repl(' ',30),1,30)+' '+tqCampos.FieldByName('Data_Type').AsString+'('+tqCampos.FieldByName('Data_Length').AsString+')'+' '+constr);

   if (tqCampos.FieldByName('DATA_TYPE').AsString = 'DATE') THEN
    xsql:=(copy(tqCampos.FieldByName('Column_Name').AsString+repl(' ',30),1,30)+' '+tqCampos.FieldByName('Data_Type').AsString+' '+constr);

   if (tqCampos.FieldByName('DATA_TYPE').AsString = 'BLOB') OR (tqCampos.FieldByName('DATA_TYPE').AsString = 'LONG RAW') THEN
    xsql:=(copy(tqCampos.FieldByName('Column_Name').AsString+repl(' ',30),1,30)+' '+tqCampos.FieldByName('Data_Type').AsString+' '+constr);

   if (tqCampos.FieldByName('DATA_TYPE').AsString = 'NUMBER') then begin
    if (tqCampos.FieldByName('DATA_SCALE').AsString = '0') then
     xsql:=(copy(tqCampos.FieldByName('Column_Name').AsString+repl(' ',30),1,30)+' SMALLINT'+' '+constr);
    if (tqCampos.FieldByName('DATA_SCALE').AsString = '') or (tqCampos.FieldByName('DATA_Precision').AsString = '') then
     xsql:=(copy(tqCampos.FieldByName('Column_Name').AsString+repl(' ',30),1,30)+' NUMBER(*)'+' '+constr);
    if (tqCampos.FieldByName('DATA_SCALE').AsString <> '0') and (tqCampos.FieldByName('DATA_SCALE').AsString <> '') then
     xsql:=(copy(tqCampos.FieldByName('Column_Name').AsString+repl(' ',30),1,30)+' NUMBER('+tqCampos.FieldByName('DATA_PRECISION').AsString+','+tqCampos.FieldByName('DATA_SCALE').AsString+') '+constr);
   end;

   tqCampos.Next;

   if tqCampos.eof then
    fim:= ''
   else
    fim:= ',';

   GravaLinha(xsql + fim);
  end;

  ultTabela:= tqTabela.FieldByName('TABLE_NAME').AsString;
  tqTabela.Next;
 end;
 GravaLinha(');');
 if constrPK <> '' then
  GravaLinha('ALTER TABLE '+ultTabela+' ADD CONSTRAINT PK_'+ultTabela+' PRIMARY KEY('+constrPK+');');
 GravaLinha('');
 GravaLinha('quit');

 CloseFile(Arq);
 Parametros.Free;
 tqTabela.Close;
 tqTabela.Free;
 tqCampos.Close;
 tqCampos.Free;
 tqConst.Close;
 tqConst.Free;
end;

end.
